package com.sit.estatemanagementserver.dao;

import com.sit.estatemanagementserver.enity.VisitInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

@Repository
public class VisitInfoDao {
    @Autowired
    DatabaseManager db;
    private static final DateTimeFormatter formatter = DateTimeFormatter.ISO_LOCAL_DATE_TIME;

    private VisitInfo resultSetToVisitInfo(ResultSet rs) throws SQLException {
        return new VisitInfo(
                rs.getString(DatabaseContract.VisitInfoEntry.COLUMN_VISITOR_ID),
                rs.getString(DatabaseContract.VisitInfoEntry.COLUMN_VISITOR_NAME),
                rs.getString(DatabaseContract.VisitInfoEntry.COLUMN_VISITOR_PHONE),
                rs.getString(DatabaseContract.VisitInfoEntry.COLUMN_ROOM_NUMBER_FK),
                rs.getString(DatabaseContract.VisitInfoEntry.COLUMN_VISIT_PURPOSE),
                LocalDateTime.parse(rs.getString(DatabaseContract.VisitInfoEntry.COLUMN_VISIT_START_TIME), formatter),
                LocalDateTime.parse(rs.getString(DatabaseContract.VisitInfoEntry.COLUMN_VISIT_END_TIME), formatter),
                rs.getString(DatabaseContract.VisitInfoEntry.COLUMN_QR_PAYLOAD) // 修改
        );
    }

    public boolean addVisitInfo(VisitInfo visitInfo) {
        String sql = "INSERT INTO " + DatabaseContract.VisitInfoEntry.TABLE_NAME + " (" +
                "visitor_id, visitor_name, visitor_phone, visiting_room_number, visit_purpose, " +
                "visit_start_time, visit_end_time, qr_code_payload) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"; // 修改
        try (Connection conn = db.connect(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            // ... 设置前7个参数 ...
            pstmt.setString(1, visitInfo.getVisitorId());
            pstmt.setString(2, visitInfo.getVisitorName());
            pstmt.setString(3, visitInfo.getVisitorPhone());
            pstmt.setString(4, visitInfo.getVisitingRoomNumber());
            pstmt.setString(5, visitInfo.getVisitPurpose());
            pstmt.setString(6, visitInfo.getVisitStartTime().format(formatter));
            pstmt.setString(7, visitInfo.getVisitEndTime().format(formatter));
            pstmt.setString(8, visitInfo.getQrCodePayload()); // 修改
            pstmt.executeUpdate();
            return true;
        } catch (SQLException e) {
            System.err.println(DatabaseManager.TAG + ": Error adding visit info: " + e.getMessage());
            return false;
        }
    }

    public boolean updateVisitInfo(VisitInfo visitInfo) {
        String sql = "UPDATE " + DatabaseContract.VisitInfoEntry.TABLE_NAME + " SET " +
                "visitor_name = ?, visitor_phone = ?, visiting_room_number = ?, visit_purpose = ?, " +
                "visit_start_time = ?, visit_end_time = ?, qr_code_payload = ? WHERE visitor_id = ?"; // 修改
        try (Connection conn = db.connect(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            // ... 设置前7个参数 ...
            pstmt.setString(1, visitInfo.getVisitorName());
            pstmt.setString(2, visitInfo.getVisitorPhone());
            pstmt.setString(3, visitInfo.getVisitingRoomNumber());
            pstmt.setString(4, visitInfo.getVisitPurpose());
            pstmt.setString(5, visitInfo.getVisitStartTime().format(formatter));
            pstmt.setString(6, visitInfo.getVisitEndTime().format(formatter));
            pstmt.setString(7, visitInfo.getQrCodePayload()); // 修改
            pstmt.setString(8, visitInfo.getVisitorId());
            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException e) {
            System.err.println(DatabaseManager.TAG + ": Error updating visit info with id '" + visitInfo.getVisitorId() + "': " + e.getMessage());
            return false;
        }
    }

    public VisitInfo getVisitInfo(String visitorId) {
        String sql = "SELECT * FROM " + DatabaseContract.VisitInfoEntry.TABLE_NAME + " WHERE " +
                DatabaseContract.VisitInfoEntry.COLUMN_VISITOR_ID + " = ?";
        try (Connection conn = db.connect(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, visitorId);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return resultSetToVisitInfo(rs);
                }
            }
        } catch (SQLException e) {
            System.err.println(DatabaseManager.TAG + ": Error getting visit info with id '" + visitorId + "': " + e.getMessage());
        }
        return null;
    }

    public List<VisitInfo> getAllVisitInfos() {
        List<VisitInfo> visitInfos = new ArrayList<>();
        String sql = "SELECT * FROM " + DatabaseContract.VisitInfoEntry.TABLE_NAME;
        try (Connection conn = db.connect();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                visitInfos.add(resultSetToVisitInfo(rs));
            }
        } catch (SQLException e) {
            System.err.println(DatabaseManager.TAG + ": Error getting all visit infos: " + e.getMessage());
        }
        return visitInfos;
    }

    public List<VisitInfo> getVisitInfosByVisitor(String visitorId) {
        List<VisitInfo> visitInfos = new ArrayList<>();
        String sql = "SELECT * FROM " + DatabaseContract.VisitInfoEntry.TABLE_NAME + " WHERE " +
                DatabaseContract.VisitInfoEntry.COLUMN_VISITOR_ID + " = ?"; // 注意: 访客ID应该在访客表唯一，这里按设计保留
        try (Connection conn = db.connect(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, visitorId);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    visitInfos.add(resultSetToVisitInfo(rs));
                }
            }
        } catch (SQLException e) {
            System.err.println(DatabaseManager.TAG + ": Error getting visit infos for visitor '" + visitorId + "': " + e.getMessage());
        }
        return visitInfos;
    }

    public List<VisitInfo> getVisitInfosByRoom(String roomNumber) {
        List<VisitInfo> visitInfos = new ArrayList<>();
        String sql = "SELECT * FROM " + DatabaseContract.VisitInfoEntry.TABLE_NAME + " WHERE " +
                DatabaseContract.VisitInfoEntry.COLUMN_ROOM_NUMBER_FK + " = ?";
        try (Connection conn = db.connect(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, roomNumber);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    visitInfos.add(resultSetToVisitInfo(rs));
                }
            }
        } catch (SQLException e) {
            System.err.println(DatabaseManager.TAG + ": Error getting visit infos for room '" + roomNumber + "': " + e.getMessage());
        }
        return visitInfos;
    }

    public boolean deleteVisitInfo(String visitorId) {
        String sql = "DELETE FROM " + DatabaseContract.VisitInfoEntry.TABLE_NAME + " WHERE " +
                DatabaseContract.VisitInfoEntry.COLUMN_VISITOR_ID + " = ?";
        try (Connection conn = db.connect(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, visitorId);
            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException e) {
            System.err.println(DatabaseManager.TAG + ": Error deleting visit info with id '" + visitorId + "': " + e.getMessage());
            return false;
        }
    }
}